package mwa.cs.dao;

import java.util.Map;

import mwa.cs.database.Database;

/**
 * A class used for managing GEN_DOC
 */
public class GenDocDAO {

    /** a database of this GEN_DOC */
    private Database db;

    /**
     * Instantiate a new object of this class
     * @param db a database
     */
    public GenDocDAO(Database db) {
        this.db = db;
    }

    /**
     * generating new 'Customer Registration' doc
     * @param pnSiteId is site_id
     * @param paYear current year
     * @return next running number
     */
    public String generatingNextCRDoc(int pnSiteId, String paMonth, String paYear) {
        String vaSql = "SELECT running FROM gen_doc WHERE site_id = ? and doc_type = 'CR'";
        Map<String, Object> result = db.querySingle(vaSql, pnSiteId);
        if (result != null) { // updating running number to next number
        	 String vaSql2 = "UPDATE gen_doc SET running = ?, month = ?, year = ? WHERE site_id = ? and doc_type = 'CR'";
             try {
             	db.update(vaSql2, String.format("%05d", Integer.parseInt(result.get("RUNNING").toString()) + 1), 
             			paMonth, paYear, pnSiteId);
             } catch (Exception e) {
             	throw new RuntimeException(e);
             }
            return String.format("%05d", Integer.parseInt(result.get("RUNNING").toString()) + 1);
        } else { // else can't find running number so generating new number
        	String vaSql2 = "INSERT INTO gen_doc (site_id, doc_type, month, year, running)" 
            		+ " VALUES(?, 'CR', ?, ?, '00001')";
        	try {
                db.insert(vaSql2, pnSiteId, paMonth, paYear);
            } catch (Exception e) {
            	throw new RuntimeException(e);
            }
            return String.format("%05d", 1);
        }
    }
    
    /**
     * generating new 'Bill Number' doc
     * @param pnSiteId is site_id
     * @param paMonth current month
     * @param paYear current year
     * @return next running number
     */
    public String generatingNextBNDoc(int pnSiteId, String paMonth, String paYear) {
        String vaSql = "SELECT running FROM gen_doc WHERE site_id = ? and doc_type = 'BN' and month = ? and year = ?";
        Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paMonth, paYear);
        if (result != null) { // updating running number to next number
        	 String vaSql2 = "UPDATE gen_doc SET running = ? WHERE site_id = ? and doc_type = 'BN' and month = ? and year = ?";
        	 String vnBillNumber = Long.toString(Long.parseLong(result.get("RUNNING").toString()) + 1);
             try {
             	db.update(vaSql2, vnBillNumber, pnSiteId, paMonth, paYear);
             } catch (Exception e) {
             	throw new RuntimeException(e);
             }
            return vnBillNumber;
        } else { // else can't find running number so generating new number
        	String vaSql2 = "INSERT INTO gen_doc (site_id, doc_type, month, year, running)" 
            		+ " VALUES(?, 'BN', ?, ?, ?)";
        	String vaBillNumber = paYear.substring(2) + paMonth + "000001";
        	try {
                db.insert(vaSql2, pnSiteId, paMonth, paYear, vaBillNumber);
            } catch (Exception e) {
            	throw new RuntimeException(e);
            }
            return vaBillNumber;
        }
    }
    
    /**
     * generating new 'Serial Number' doc
     * @param pnSiteId is site_id
     * @param paMonth current month
     * @param paYear current year
     * @return next running number
     */
    public String generatingNextSNDoc(int pnSiteId, String paMonth, String paYear) {
        String vaSql = "SELECT running FROM gen_doc WHERE site_id = ? and doc_type = 'SN' and month = ? and year = ?";
        Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paMonth, paYear);
        if (result != null) { // updating running number to next number
        	 String vaSql2 = "UPDATE gen_doc SET running = ? WHERE site_id = ? and doc_type = 'SN' and month = ? and year = ?";
        	 String vnBillNumber = Long.toString(Long.parseLong(result.get("RUNNING").toString()) + 1);
             try {
             	db.update(vaSql2, vnBillNumber, pnSiteId, paMonth, paYear);
             } catch (Exception e) {
             	throw new RuntimeException(e);
             }
            return vnBillNumber;
        } else { // else can't find running number so generating new number
        	String vaSql2 = "INSERT INTO gen_doc (site_id, doc_type, month, year, running)" 
            		+ " VALUES(?, 'SN', ?, ?, ?)";
        	String vaBillNumber = paYear.substring(2) + paMonth + "000001";
        	try {
                db.insert(vaSql2, pnSiteId, paMonth, paYear, vaBillNumber);
            } catch (Exception e) {
            	throw new RuntimeException(e);
            }
            return vaBillNumber;
        }
    }
    
    /**
     * generating new 'Request Number' doc
     * @param pnSiteId is site_id
     * @param paMonth current month
     * @param paYear current year
     * @return next running number
     */
    public String generatingNextRQDoc(int pnSiteId, String paMonth, String paYear) {
        String vaSql = "SELECT running FROM gen_doc WHERE site_id = ? and doc_type = 'RQ' and month = ? and year = ?";
        Map<String, Object> result = db.querySingle(vaSql, pnSiteId, paMonth, paYear);
        if (result != null) { // updating running number to next number
        	 String vaSql2 = "UPDATE gen_doc SET running = ? WHERE site_id = ? and doc_type = 'RQ' and month = ? and year = ?";
        	 String vaRequestNumber = Long.toString(Long.parseLong(result.get("RUNNING").toString()) + 1);
             try {
             	db.update(vaSql2, vaRequestNumber, pnSiteId, paMonth, paYear);
             } catch (Exception e) {
             	throw new RuntimeException(e);
             }
            return vaRequestNumber;
        } else { // else can't find running number so generating new number
        	String vaSql2 = "INSERT INTO gen_doc (site_id, doc_type, month, year, running)" 
            		+ " VALUES(?, 'RQ', ?, ?, ?)";
        	String vaRequestNumber = paYear.substring(2) + paMonth + "000001";
        	try {
                db.insert(vaSql2, pnSiteId, paMonth, paYear, vaRequestNumber);
            } catch (Exception e) {
            	throw new RuntimeException(e);
            }
            return vaRequestNumber;
        }
    }
    
    /**
     * generating new 'User Id' doc
     * @return next running number
     */
    public String generatingNextUIDoc() {
        String vaSql = "SELECT running FROM gen_doc WHERE doc_type = 'UI'";
        Map<String, Object> result = db.querySingle(vaSql);
        if (result != null) { // updating running number to next number
        	 String vaSql2 = "UPDATE gen_doc SET running = ? WHERE doc_type = 'UI'";
             try {
             	db.update(vaSql2, String.format("%01d", Integer.parseInt(result.get("RUNNING").toString()) + 1));
             } catch (Exception e) {
             	throw new RuntimeException(e);
             }
            return String.format("%01d", Integer.parseInt(result.get("RUNNING").toString()) + 1);
        } else { // else can't find running number so generating new number
        	String vaSql2 = "INSERT INTO gen_doc (site_id, doc_type, month, year, running)" 
            		+ " VALUES(0, 'UI', '00', '0000', '1')";
        	try {
                db.insert(vaSql2);
            } catch (Exception e) {
            	throw new RuntimeException(e);
            }
            return String.format("%01d", 1);
        }
    }
    
    /**
     * generating new 'Image Number' doc
     * @return next running number
     */
    public String generatingNextMNDoc() {
        String vaSql = "SELECT running FROM gen_doc WHERE doc_type = 'MN'";
        Map<String, Object> result = db.querySingle(vaSql);
        if (result != null) { // updating running number to next number
        	 String vaSql2 = "UPDATE gen_doc SET running = ? WHERE doc_type = 'MN'";
             try {
             	db.update(vaSql2, String.format("%01d", Integer.parseInt(result.get("RUNNING").toString()) + 1));
             } catch (Exception e) {
             	throw new RuntimeException(e);
             }
            return String.format("%01d", Integer.parseInt(result.get("RUNNING").toString()) + 1);
        } else { // else can't find running number so generating new number
        	String vaSql2 = "INSERT INTO gen_doc (site_id, doc_type, month, year, running)" 
            		+ " VALUES(0, 'MN', '00', '0000', '1')";
        	try {
                db.insert(vaSql2);
            } catch (Exception e) {
            	throw new RuntimeException(e);
            }
            return String.format("%01d", 1);
        }
    }
    
    //Excel File
    public String generatingNextExDoc() {
        String vaSql = "SELECT running FROM gen_doc WHERE doc_type = 'EX'";
        Map<String, Object> result = db.querySingle(vaSql);
        if (result != null) { // updating running number to next number
        	 String vaSql2 = "UPDATE gen_doc SET running = ? WHERE doc_type = 'EX'";
             try {
             	db.update(vaSql2, String.format("%01d", Integer.parseInt(result.get("RUNNING").toString()) + 1));
             } catch (Exception e) {
             	throw new RuntimeException(e);
             }
            return String.format("%01d", Integer.parseInt(result.get("RUNNING").toString()) + 1);
        } else { // else can't find running number so generating new number
        	String vaSql2 = "INSERT INTO gen_doc (site_id, doc_type, month, year, running)" 
            		+ " VALUES(0, 'EX', '00', '0000', '1')";
        	try {
                db.insert(vaSql2);
            } catch (Exception e) {
            	throw new RuntimeException(e);
            }
            return String.format("%01d", 1);
        }
    }
    
}
